/*
 * PurchaseOrderDAO.java
 *
 * Created on February 28, 2007, 8:58 AM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.util.ArrayList;

/** Reads and writes PurchaseOrderBO data to and from the database.
 *
 * @author Travis Kulbeth
 */
public class PurchaseOrderDAO {
    
    /** Contains an instance of PurchaseOrderDAO*/
    private static PurchaseOrderDAO instance = null;
    
    /** Creates a new instance of PurchaseOrderDAO */
    public PurchaseOrderDAO() {
    }
    
    /**Retrieves an instance of the PurchaseOrderDAO object
     *
     * @return a current singleton instance of the PurchaseOrderDAO class
     */
    public static synchronized PurchaseOrderDAO getInstance() {
        if (instance == null) {
            instance = new PurchaseOrderDAO();
        }
        return instance;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   CREATE
    /**
     * Creates a new PurchaseOrderBO object with a newly generated GUID.
     * 
     * @return a newly created PurchaseOrderBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public PurchaseOrderBO create() throws Exception{
        String id = GUID.generate();
        PurchaseOrderBO bo = new PurchaseOrderBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getPOID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing PurchaseOrder record
     * from the database based on the PurchaseOrder id and returns a PurchaseOrderBO.
     * 
     * 
     * @return a PurchaseOrderBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized PurchaseOrderBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        PurchaseOrderBO trans = (PurchaseOrderBO)c.get(id);
        if (trans != null){
            return trans;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            trans = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for PurchaseOrder ID = " + id, e);
        }catch (ConnectionPoolException x) {
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return trans;
    }
    
    /**
     * reads a purchase order by vendor id
     * 
     * 
     * @return purchase order BO
     * @param vendID contains the vendor id
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized PurchaseOrderBO readByVendorID(String vendID) throws DataException, ConnectionPoolException{
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        PurchaseOrderBO trans = (PurchaseOrderBO)c.get(vendID);
        if (trans != null){
            return trans;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            trans = this.read(vendID, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for PurchaseOrder ID = " + vendID, e);
        }catch (ConnectionPoolException x) {
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return trans;
        
        
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing PurchaseOrder record
     * from the database based on the PurchaseOrder id, using an already existing connection to the DB,
     * and returns a PurchaseOrderBO.
     * 
     * @return PurchaseOrderBO
     * @param id contains the given id for the PurchaseOrder to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized PurchaseOrderBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        VendorDAO vendDAO = VendorDAO.getInstance();
        OrderLineDAO orderLineDAO = OrderLineDAO.getInstance();
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        PurchaseOrderBO POBO = (PurchaseOrderBO)c.get(id);
        if (POBO != null){
            return POBO;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM PurchaseOrder WHERE POID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            POBO = new PurchaseOrderBO(rs.getString("POID"));
            POBO.setPOTotal(rs.getDouble("POTotal"));
            POBO.setVendor(vendDAO.read(rs.getString("vendorID")));
            
            String POID = POBO.getPOID();
            
            ResultSet rs3 = stmt.executeQuery("SELECT * FROM OrderLine WHERE PurchaseOrderID = '" + POID + "'");
            
                while(rs3.next()){
                    
                    String orderLineID = rs3.getString("id");
                    
                    POBO.addOrderLine(orderLineDAO.read(orderLineID, conn));
                
                }//end while
            
            
            c.put(POBO.getPOID(), POBO);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return POBO;
    }
         
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) wants to save or update an object
     * into the database.
     * @param POIn contains POIn
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(PurchaseOrderBO POIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(POIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Error saving record for PurchaseOrder ID = " + POIn.getPOID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /**
     * This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the PurchaseOrderDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.
     * @param POBO contains POBO
     * @param conn contains the Connection that has been established to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized void save(PurchaseOrderBO POBO, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // call save(bo, conn) on any subobjects (like PurchaseOrderDAO to MembershipDAO)
                
        // run update or insert
        if (POBO.isIsDirty()) {
            if (POBO.isAlreadyInDB()) {
                update(POBO, conn);
            }else{
                insert(POBO, conn);
            }
            // set the dirty flag to false now that we've saved it
            POBO.setIsDirty(false);
        }
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(POBO.getPOID(), POBO);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * @param POBO contains POBO
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    private synchronized void update(PurchaseOrderBO POBO, Connection conn) throws SQLException,
            DataException, ConnectionPoolException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE PurchaseOrder SET POID = ?, POTotal = ?, VendorID = ? WHERE POID = ?");
        pstmt.setString(1, POBO.getPOID());
        pstmt.setDouble(2, POBO.getPOTotal());
        pstmt.setString(3, POBO.getVendor().getVendorID());
        pstmt.setString(4, POBO.getPOID());
        pstmt.executeUpdate();
                      
        pstmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * @param POBO contains the POBO to be saved
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    private synchronized void insert(PurchaseOrderBO POBO, Connection conn) throws SQLException,
            DataException, ConnectionPoolException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO PurchaseOrder (POID, POTotal, VendorID) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, POBO.getPOID());
        pstmt.setDouble(2, POBO.getPOTotal());
        pstmt.setString(3, POBO.getVendor().getVendorID());
        pstmt.executeUpdate();
        pstmt.close();
        
        POBO.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /** I did not include the DELETE functionality intentionally due to instructions received, as well as
     * to protect the integrity of the data.
     */
}
